package dao;

import beans.Despesa;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class DespesaDAO {

	PreparedStatement pst;
	ResultSet rs;

	public DespesaDAO() {
	}

	public Despesa load(int id) {
		Despesa dsp = new Despesa();

		String query = "SELECT * FROM despesa WHERE id = ?";

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			pst.setInt(1, id);

			rs = pst.executeQuery();

			if (rs.next()) {
				dsp.setId(rs.getInt("id"));
				dsp.setData(rs.getDate("data"));
				dsp.setValor(rs.getFloat("valor"));
				dsp.setNotaFiscal(rs.getInt("nota_fiscal"));
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

		return dsp;
	}

	public int insert(Despesa dsp) {
		String query = "INSERT INTO despesa (data, valor, nota_fiscal) VALUES(?, ?, ?) RETURNING id";
		int id = 0;

		try {
			pst = Conexao.getConexao().prepareStatement(query);

			pst.setDate(1, dsp.getData());
			pst.setFloat(2, dsp.getValor());
			pst.setInt(3, dsp.getNotaFiscal());

			rs = pst.executeQuery();
			
			if (rs.next())
			{
				id = rs.getInt(1);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		
		return id;
	}

	private void update(Despesa dsp) {
		String query = "UPDATE despesa SET data = ?, valor = ?, nota_fiscal = ? WHERE id = ?";

		try {
			pst = Conexao.getConexao().prepareStatement(query);

			pst.setDate(1, dsp.getData());
			pst.setFloat(2, dsp.getValor());
			pst.setInt(3, dsp.getNotaFiscal());
			pst.setInt(4, dsp.getId());

			pst.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}


	}

	public int save(Despesa dsp) {
		String query = "SELECT COUNT(*) FROM despesa WHERE id = ?";
		int serialNum = 0;

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			pst.setInt(1, dsp.getId());

			rs = pst.executeQuery();
			rs.next();

			if (rs.getInt("count") == 0) {
				serialNum = insert(dsp);
			} else {
				update(dsp);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}

		return serialNum;
	}

	public void delete(Despesa dsp) {
		String query = "DELETE FROM despesa WHERE id = ?";

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			pst.setInt(1, dsp.getId());

			pst.executeUpdate();

		} catch (SQLException ex) {
			ex.printStackTrace();

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}
	}

	public ArrayList<Despesa> loadAll() {
		ArrayList<Despesa> despesas = new ArrayList<Despesa>();

		String query = "SELECT * FROM despesa";

		try {
			pst = Conexao.getConexao().prepareStatement(query);
			rs = pst.executeQuery();

			while (rs.next()) {
				Despesa dsp = new Despesa();

				dsp.setId(rs.getInt("id"));
				dsp.setData(rs.getDate("data"));
				dsp.setValor(rs.getFloat("valor"));
				dsp.setNotaFiscal(rs.getInt("nota_fiscal"));

				despesas.add(dsp);
			}
		} catch (SQLException ex) {
			ex.getLocalizedMessage();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
			} catch (SQLException ex) {
				ex.getLocalizedMessage();
			}
		}

		return despesas;
	}
}
